/* ********** Project table ********** */

CREATE TABLE Project
(
	ProjectID        NUMBER(38) NOT NULL PRIMARY KEY,
	Name      	 	 VARCHAR2(50) NOT NULL,
	DateCreate       DATE NOT NULL,
	DateStart        DATE NOT NULL,
	DateFinish       DATE NULL,
	Status           NUMBER(2) NOT NULL,
  Description      VARCHAR2(1000) NULL
);

CREATE SEQUENCE Project_ProjectID_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;

CREATE OR REPLACE TRIGGER SET_Project_ProjectID BEFORE INSERT ON Project FOR EACH ROW
BEGIN
  SELECT Project_ProjectID_SEQ.NEXTVAL INTO :NEW.ProjectID FROM DUAL;
END;
/
/* ********** Developer table ********** */

CREATE TABLE Developer
(
	DeveloperID           NUMBER(38) NOT NULL PRIMARY KEY,
	aspnet_UsersID        RAW(16) NULL,
	Name          		  VARCHAR2(50) NOT NULL
);

CREATE SEQUENCE Developer_DeveloperID_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;

CREATE OR REPLACE TRIGGER SET_Developer_DeveloperID  BEFORE INSERT ON Developer FOR EACH ROW
BEGIN
  SELECT Developer_DeveloperID_SEQ.NEXTVAL INTO :NEW.DeveloperID FROM DUAL;
END;
/
/* ********** ReportingPeriod table ********** */

CREATE TABLE ReportingPeriod
(
	ReportingPeriodID       NUMBER(38) NOT NULL PRIMARY KEY,
	DateStart               DATE NOT NULL,
	DateFinish              DATE NOT NULL,
	ISFinished				NUMBER(1) NOT NULL
	
);

CREATE SEQUENCE ReportingPeriod_ID_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;

CREATE OR REPLACE TRIGGER SET_ReportingPeriod_ID BEFORE INSERT ON ReportingPeriod FOR EACH ROW
BEGIN
  SELECT ReportingPeriod_ID_SEQ.NEXTVAL INTO :NEW.ReportingPeriodID FROM DUAL;
END;
/
/* ********** DeveloperProject table ********** */

CREATE TABLE DeveloperProject
(
	DeveloperProjectID    NUMBER(38) NOT NULL PRIMARY KEY,
	DeveloperID        	  NUMBER(38) NOT NULL,
	ProjectID        	  NUMBER(38) NOT NULL
);

CREATE SEQUENCE DeveloperProject_ID_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;

CREATE OR REPLACE TRIGGER SET_DeveloperProject_ID BEFORE INSERT ON DeveloperProject FOR EACH ROW
BEGIN
  SELECT DeveloperProject_ID_SEQ.NEXTVAL INTO :NEW.DeveloperProjectID FROM DUAL;
END;
/
/* ********** Report table ********** */
CREATE TABLE Report
(
	ReportID        	NUMBER(38) NOT NULL PRIMARY KEY,
	Title       		VARCHAR2(500) NOT NULL,
	Description         VARCHAR2(4000) NOT NULL,
	Hours            	NUMBER(4,2) NOT NULL,
	State  				NUMBER(2) NOT NULL,
	DateCreate      	Date NOT NULL,
	DateReporting       Date NOT NULL,
	DateModified     	Date NULL,
	ReportingPeriodID 	NUMBER(38) NOT NULL,
	DeveloperProjectID 	NUMBER(38) NOT NULL
);

CREATE SEQUENCE Report_ReportID_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;

CREATE OR REPLACE TRIGGER SET_Report_ReportID BEFORE INSERT ON Report FOR EACH ROW
BEGIN
  SELECT Report_ReportID_SEQ.NEXTVAL INTO :NEW.ReportID FROM DUAL;
END;
/
/* ********** Constraint ********** */

alter table DeveloperProject add CONSTRAINT FK_Project_DeveloperProject FOREIGN KEY (ProjectID) REFERENCES Project (ProjectID);
alter table DeveloperProject add CONSTRAINT FK_Developer_DeveloperProject FOREIGN KEY (DeveloperID) REFERENCES Developer (DeveloperID);
alter table Report add CONSTRAINT FK_DeveloperProject_Report FOREIGN KEY (DeveloperProjectID) REFERENCES DeveloperProject (DeveloperProjectID);
alter table Report add CONSTRAINT FK_ReportingPeriod_Report FOREIGN KEY (ReportingPeriodID) REFERENCES ReportingPeriod (ReportingPeriodID);
